/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package nexus.criterios;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import nexus.adec.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Set;
import nexus.criterios.exp.Expresion;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

/**
 *
 * @author Rodrigo
 */
public class GestorCriteriosDB {
    private Coneccion con;
    private static GestorCriteriosDB instance;
    public static final String NO_EXISTE = "no existe";

    private GestorCriteriosDB(){
        con = Coneccion.getInstance();
    }

    public CriterioEvaluacion crearCriterioEvaluacion(String codigoEspacio){
        CriterioEvaluacion res = null;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("insert into criterio_evaluacion values(?,?,?,?,?);");

            String codigoPadre = obtenerCodigoCriterio();
            ps.setString(1, codigoPadre);
            ps.setNull(2, Types.VARCHAR);
            ps.setNull(3, Types.VARCHAR);
            ps.setString(4, codigoEspacio);
            ps.setNull(5, Types.VARCHAR);
            ps.executeUpdate();

            ps.setString(1, obtenerCodigoCriterio());
            ps.setString(2, codigoPadre);
            ps.setNull(3, Types.VARCHAR);
            ps.setString(4, "Primer Parcial");
            ps.setNull(5, Types.VARCHAR);
            ps.executeUpdate();


            ps.setString(1, obtenerCodigoCriterio());
            ps.setString(2, codigoPadre);
            ps.setNull(3, Types.VARCHAR);
            ps.setString(4, "Segundo Parcial");
            ps.setNull(5, Types.VARCHAR);
            ps.executeUpdate();

            ps.setString(1, obtenerCodigoCriterio());
            ps.setString(2, codigoPadre);
            ps.setNull(3, Types.VARCHAR);
            ps.setString(4, "Examen Final");
            ps.setNull(5, Types.VARCHAR);
            ps.executeUpdate();

            ps.setString(1, obtenerCodigoCriterio());
            ps.setString(2, codigoPadre);
            ps.setNull(3, Types.VARCHAR);
            ps.setString(4, "Segunda Instancia");
            ps.setNull(5, Types.VARCHAR);
            ps.executeUpdate();

            PreparedStatement psi = c.prepareStatement("insert into asignacion_criterio values(?,?);");
            psi.setString(1, codigoPadre);
            psi.setString(2, codigoEspacio);
            psi.executeUpdate();

            res = recuperarCriterioEvaluacion(codigoPadre);

            psi.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return res;
    }

   
    
    public CriterioEvaluacion recuperarCriterioEvaluacion(String codigoCriterio){
        CriterioEvaluacion res = null;
        ArrayList<CriterioEvaluacion> sbc = new ArrayList<CriterioEvaluacion>();

        try {
            Connection c = con.getConnection();

            //Recuperamos datos del criterio
            Statement stm = c.createStatement();
            ResultSet rs = stm.executeQuery("select * from criterio_evaluacion where codigo_criterio='"+codigoCriterio+"';");
            rs.next();
            res = new CriterioEvaluacion(rs.getString("codigo_criterio"), rs.getString("nombre_criterio"));
            String criterioCalificacion = rs.getString("nombre_criterio_calificacion");

            //Recuperamos el Criterio de Calificacion.
            CriterioCalificacion cf = obtenerCriterioCalificacion(criterioCalificacion);
            res.setCriterioCalificacion(cf);

            //Recuperamos a los hijos.
            rs.close();
            rs = stm.executeQuery("select * from criterio_evaluacion where cri_codigo_criterio='"+codigoCriterio+"';");
            ArrayList<String> codigos = new ArrayList<String>();
            while(rs.next()){
                 String codigo = rs.getString("codigo_criterio");
                 codigos.add(codigo);
            }
            rs.close();
            stm.close();
            //c.close();
            for(String codigo:codigos){   
                CriterioEvaluacion cri = recuperarCriterioEvaluacion(codigo);
                sbc.add(cri);
            }
            
            res.setSubcriterios(sbc);
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }

    public CriterioCalificacion obtenerCriterioCalificacion(String nombreCriterio){
        CriterioCalificacion res = null;
        try {
            Connection c = con.getConnection();
            Statement stm = c.createStatement();
            if(esCualitativo(nombreCriterio)){
                ResultSet rs = stm.executeQuery("select * from item_cualitativo where nombre_criterio_calificacion='"+nombreCriterio+"'");
                HashMap<String,Double> map = new HashMap<String, Double>();
                while(rs.next()){
                    map.put(rs.getString("nombre_item"), (double)rs.getInt("valor_item"));
                }
                res = new CriterioCalificacionCualitativo(nombreCriterio);
                ((CriterioCalificacionCualitativo)res).setValores(map);
                rs.close();
            }
            if(esCuantitativo(nombreCriterio)){
                ResultSet rs = stm.executeQuery("select * from criterio_cuantitativo where nombre_criterio_calificacion='"+nombreCriterio+"'");
                rs.next();
                res = new CriterioCalificacionCuantitativo(nombreCriterio,rs.getInt("minimo"), rs.getInt("maximo"));
                rs.close();
            }
            stm.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    private boolean esCualitativo(String nombreCriterio){
        boolean res = false;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_cualitativo where nombre_criterio_calificacion=?;");
            ps.setString(1, nombreCriterio);
            ResultSet rs = ps.executeQuery();
            res = rs.next();
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    private boolean esCuantitativo(String nombreCriterio){
        boolean res = false;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_cuantitativo where nombre_criterio_calificacion=?;");
            ps.setString(1, nombreCriterio);
            ResultSet rs = ps.executeQuery();
            res = rs.next();
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }

    public String verificarAsignacionCriterio(String codigoEspacio){
        String res = NO_EXISTE;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from asignacion_criterio where codigo_espacio=?;");
            ps.setString(1, codigoEspacio);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                res = rs.getString("codigo_criterio");
            }
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }

    private String obtenerCodigoCriterio(){
        String res = "";
        try {
            Connection c = con.getConnection();
            PreparedStatement pss = c.prepareStatement("select nextval('evaluacion_seq');");
            ResultSet rs = pss.executeQuery();
            rs.next();
            res = "CRI"+rs.getInt(1);
            rs.close();
            pss.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }

    public ArrayList<CriterioCalificacion> listarCriteriosCalificacionUsuario(String nombreUsuario){
        ArrayList<CriterioCalificacion> res = new ArrayList<CriterioCalificacion>();
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_calificacion where nombre_usuario=?");
            ps.setString(1, nombreUsuario);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                res.add(obtenerCriterioCalificacion(rs.getString("nombre_criterio_calificacion")));
            }
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }

    public void guardarCriterioCualitativo(String nombreUsuario,CriterioCalificacionCualitativo ccc){
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("insert into criterio_calificacion values(?,?);");
            ps.setString(1, ccc.getNombreCriterio());
            ps.setString(2, nombreUsuario);
            int count = ps.executeUpdate();

            if(count > 0){
                ps = c.prepareStatement("insert into criterio_cualitativo values(?);");
                ps.setString(1, ccc.getNombreCriterio());
                ps.executeUpdate();

                ps = c.prepareStatement("insert into item_cualitativo values(?,?,?);");
                Set<String> s = ccc.getValores().keySet();
                for(String k:s){
                    ps.setString(1, ccc.getNombreCriterio());
                    ps.setString(2, k);
                    ps.setInt(3, ccc.getValores().get(k).intValue());
                    ps.executeUpdate();
                }
            }
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void guardarCriterioCuantitativo(String nombreUsuario, CriterioCalificacionCuantitativo ccu){
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("insert into criterio_calificacion values(?,?);");
            ps.setString(1, ccu.getNombreCriterio());
            ps.setString(2, nombreUsuario);
            int count = ps.executeUpdate();

            if(count > 0){
                ps = c.prepareStatement("insert into criterio_cuantitativo values(?,?,?);");
                ps.setString(1, ccu.getNombreCriterio());
                ps.setInt(2, ccu.getMinimo());
                ps.setInt(3, ccu.getMaximo());
                ps.executeUpdate();
            }
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }


    public void guardarFormula(String codigoCriterio,String formula){
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("update criterio_evaluacion set formula=? where codigo_criterio=?;");
            ps.setString(1, formula);
            ps.setString(2, codigoCriterio);
            ps.executeUpdate();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public String recuperarFormula(String codigoCriterio){
        String res = "";
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_evaluacion where codigo_criterio=?;");
            ps.setString(1, codigoCriterio);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                res = rs.getString("formula");
            }
            rs.close();
            ps.close();
            //c.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }

    public void crearCriterioEvaluacion(String nombreCriterio,String codigoPadre){
        try{
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("insert into criterio_evaluacion values(?,?,?,?,?);");
            ps.setString(1,obtenerCodigoCriterio());
            ps.setString(2,codigoPadre);
            ps.setNull(3, Types.VARCHAR);
            ps.setString(4,nombreCriterio);
            ps.setNull(5,Types.VARCHAR);
            ps.executeUpdate();
            ps.close();
            //c.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void eliminarCriterioEvaluacion(String codigoCriterio){
        try {
            Connection c = con.getConnection();
            Statement stm = c.createStatement();
            CriterioEvaluacion ce = recuperarCriterioEvaluacion(codigoCriterio);
            ArrayList<CriterioEvaluacion> ces = ce.getSubcriterios();
            for(CriterioEvaluacion cs:ces){
                eliminarCriterioEvaluacion(cs.getCodigoCriterio());
            }
            //falta borrar de la tabla calificacion
            stm.executeUpdate("delete from asignacion_criterio where codigo_criterio='"+codigoCriterio+"';");
            stm.executeUpdate("delete from criterio_evaluacion where codigo_criterio = '"+codigoCriterio+"';");
            stm.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void asignarCriterioCalificacion(String codigoCriterioevaluacion,String codigoCriterioCalificacion){
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("update criterio_evaluacion set nombre_criterio_calificacion=? where codigo_criterio=?;");
            ps.setString(1, codigoCriterioCalificacion);
            ps.setString(2, codigoCriterioevaluacion);
            ps.executeUpdate();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void quitarCriterioCalificacion(String codigoCriterioevaluacion){
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("update criterio_evaluacion set nombre_criterio_calificacion=? where codigo_criterio=?;");
            ps.setNull(1, Types.VARCHAR);
            ps.setString(2, codigoCriterioevaluacion);
            ps.executeUpdate();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    public boolean verificarDependenciaCriterioCalificacion(String nombreCriterio){
        boolean res = true;
        try {
            Connection c = con.getConnection();
            Statement stm = c.createStatement();
            ResultSet rs = stm.executeQuery("select * from criterio_evaluacion where nombre_criterio_calificacion='"+nombreCriterio+"';");
            res = rs.next();
            rs.close();
            stm.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    
    /*   //String codigoEspacio,String codigoMateria,String codigoCarrera,String numeroGrupo
    public boolean eliminarCriterioCalificacion(String codigo){
        boolean res = false;
        try {
            Connection c = con.getConnection();
            ArrayList<String> resp = new ArrayList<String>();
            PreparedStatement ps = c.prepareStatement("select nombre_criterio from criterio_evaluacion where nombre_criterio_calificacion="+codigo+";");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                resp.add(rs.getString("nombre_criterio"));
            }
            if(resp.size()==0) {
                Statement stm = c.createStatement();
                stm.executeUpdate("delete from asignacion_criterio where nombre_criterio_calificacion ='"+codigo+"';");
                stm.executeUpdate("delete from criterio_evaluacion where nombre_criterio_calificacion ='"+codigo+"';");
                stm.executeUpdate("delete from asignacion_criterio where nombre_criterio_calificacion ='"+codigo+"';");
                stm.executeUpdate("delete from item_cualitativo where nombre_criterio_calificacion ='"+codigo+"';");
                stm.executeUpdate("delete from criterio_cualitativo where nombre_criterio_calificacion ='"+codigo+"';");
                stm.executeUpdate("delete from criterio_cuantitativo where nombre_criterio_calificacion ='"+codigo+"';");
                stm.executeUpdate("delete from criterio_calificacion where nombre_criterio_calificacion ='"+codigo+"';");
                stm.close();    
                res = true;                
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            res = false;
        }
        return res;
    }
    */
    
    public boolean eliminarCriterioCalificacion(String nombreCriterio){
        boolean res = false;
        try {
            if(!verificarDependenciaCriterioCalificacion(nombreCriterio)){
                Connection c = con.getConnection();
                Statement stm = c.createStatement();
                stm.executeUpdate("delete from criterio_cuantitativo where nombre_criterio_calificacion='"+nombreCriterio+"';");
                stm.executeUpdate("delete from item_cualitativo where nombre_criterio_calificacion='"+nombreCriterio+"';");
                stm.executeUpdate("delete from criterio_cualitativo where nombre_criterio_calificacion='"+nombreCriterio+"';");
                stm.executeUpdate("delete from criterio_calificacion where nombre_criterio_calificacion='"+nombreCriterio+"';");
                stm.close();
                //c.close();
                res = true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            res = false;
        }
        return res;
    }
    
    /** A PARTIR DE AQUI SUBSISTEMA DE NOTAS **/
    public boolean esCalificacionValida(Calificacion calificacion){
        boolean res = false;
        CriterioCalificacion cc = recuperarCriterioCalificacion(calificacion.getCodigoCriterio());
        if(cc == null)
            res = false;
        else
        if(cc instanceof CriterioCalificacionCuantitativo){
            CriterioCalificacionCuantitativo ccc = (CriterioCalificacionCuantitativo)cc;
            int nota = -1;
            try{
                nota = Integer.parseInt(calificacion.getNombreCalificacion());
            }catch(Exception ex){
                nota = -1;
            }
            if(nota>=ccc.getMinimo() && nota<= ccc.getMaximo()){
                res = true;
            }else{
                res = false;
            }
        }else{
            CriterioCalificacionCualitativo ccc = (CriterioCalificacionCualitativo)cc;
            HashMap<String,Double> map = ccc.getValores();
            Double valor = map.get(calificacion.getNombreCalificacion());
            if(valor!=null){
                res = true;
            }else{
                res = false;
            }
        }
        return res;
    }
    
    
    @Deprecated
    public void guardarCalificacion(Calificacion calificacion){
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("insert into calificacion values(?,?,?,?,?);");
            ps.setString(2, calificacion.getCodigoEspacio());
            ps.setString(1, calificacion.getNombreUsuario());
            ps.setString(3, calificacion.getCodigoCriterio());
            ps.setString(4, calificacion.getNombreCalificacion());
            ps.setInt(5, calificacion.getValorCalificacion());
            ps.executeUpdate();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    public void guardarCalificaciones(ArrayList<Calificacion> calificaciones)throws Exception{
        String res = calificaciones.size()>0 ? verificarCalificaciones(calificaciones):"";
        if(res.equals("")){
            try {
                Connection c = con.getConnection();
                PreparedStatement pss = c.prepareStatement("delete from calificacion where codigo_espacio=? and nombre_usuario=? and codigo_criterio=?;");
                PreparedStatement ps = c.prepareStatement("insert into calificacion values(?,?,?,?,?);");
                for(Calificacion calificacion:calificaciones){
                    pss.setString(1, calificacion.getCodigoEspacio());
                    pss.setString(2, calificacion.getNombreUsuario());
                    pss.setString(3, calificacion.getCodigoCriterio());
                    pss.executeUpdate();
                    
                    ps.setString(2, calificacion.getCodigoEspacio());
                    ps.setString(1, calificacion.getNombreUsuario());
                    ps.setString(3, calificacion.getCodigoCriterio());
                    ps.setString(4, calificacion.getNombreCalificacion());
                    ps.setInt(5, calificacion.getValorCalificacion());
                    ps.executeUpdate();
                }
                pss.close();
                ps.close();
                //c.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }else{
            throw new Exception(res+"NO SE REALIZO NINGUNA MODIFICACION");
        }
    }
    
    public String verificarCalificaciones(ArrayList<Calificacion> calificaciones)throws Exception{
        String res = "";
        Calificacion cal = calificaciones.get(0);
        if(tieneCriterioCalificacion(cal.getCodigoCriterio())){
            for(Calificacion c :calificaciones){
                if(!esCalificacionValida(c)){
                    res += "TIENE CALIFICACION NO VALIDA " + c.getNombreUsuario() + "\n";
                }
                if(!existeUsuario(c.getNombreUsuario(),c.getCodigoEspacio())){
                    res +="El USUARIO " + c.getNombreUsuario() + " NO ESTA INSCRITO\n";
                }
            }
        }else{
            res += "NO TIENE UN CRITERIO DE CALIFICACION ASIGNADO\n";
        }
        return res;
    }
    
    public boolean existeUsuario(String nombreusuario,String codigoEspacio){
        boolean res = false;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from asignacion_espacio where nombre_usuario=? and codigo_espacio=?;");
            ps.setString(1, nombreusuario);
            ps.setString(2, codigoEspacio);
            ResultSet rs = ps.executeQuery();
            res = rs.next();
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    
    public void borrarCalificacion(String codigoCriterio){
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("delete from calificacion where codigo_criterio=?;");
            ps.setString(1, codigoCriterio);
            ps.executeUpdate();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    public ArrayList<CriterioEvaluacion> listarCriteriosEspacio(String codigoEspacio){
        ArrayList<CriterioEvaluacion> res = new ArrayList<CriterioEvaluacion>();
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select codigo_criterio from asignacion_criterio where codigo_espacio=?;");
            ps.setString(1, codigoEspacio);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                res = listarCriterios(rs.getString("codigo_criterio"));
            }
            rs.close();
            ps.close();
            //c.close();
            if(res.size()>0) res.remove(0);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    
    private ArrayList<CriterioEvaluacion> listarCriterios(String codigoCriterio){
        ArrayList<CriterioEvaluacion> res = new ArrayList<CriterioEvaluacion>();
        CriterioEvaluacion cri = recuperarCriterioEvaluacion(codigoCriterio);
        res.add(cri);
        ArrayList<CriterioEvaluacion> hijos = cri.getSubcriterios();
        for(CriterioEvaluacion ce:hijos){
            res.addAll(listarCriterios(ce.getCodigoCriterio()));
        }
        
        return res;
    }
    
    public CriterioEvaluacion obtenerCrierioEvaluacion(String codigoCriterio){
        CriterioEvaluacion res = null;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_evaluacion where codigo_criterio=?;");
            ps.setString(1, codigoCriterio);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                res = new CriterioEvaluacion(rs.getString("codigo_criterio"), rs.getString("nombre_criterio"));
            }
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return res;
    }
    
    public ByteArrayInputStream recuperarCalificaciones(String codigoEspacio,ArrayList<GrupoAcademico> grupos,ArrayList<String> criterios){
        boolean comment = false;
        ByteArrayInputStream is = null;
        HSSFWorkbook res = new HSSFWorkbook();
        HSSFSheet sheet = res.createSheet();
        HSSFRow row = null;
        // create 2 fonts objects
        HSSFFont f = res.createFont();
        HSSFFont f2 = res.createFont();
        
        //Estilo para las cabeceras.
        
        HSSFCellStyle cellstyle = res.createCellStyle();
        cellstyle.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
        CellStyle cellblue = res.createCellStyle();
        cellblue.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        cellblue.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //cellblue.setFillForegroundColor(HSSFColor.BLUE.index);
        
        
        // Set font 1 to 12 point type, blue and bold
        f.setFontHeightInPoints((short) 12);
        f.setColor( HSSFColor.RED.index );
        f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        
        // Set font 2 to 10 point type, red and bold
        f2.setFontHeightInPoints((short) 10);
        f2.setColor( HSSFColor.RED.index);
        f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        row = sheet.createRow(1);
        row.createCell(1).setCellValue("Carrera");
        row.createCell(2).setCellValue("Materia");
        row.createCell(3).setCellValue("Grupo");
        row.createCell(4).setCellValue("CodigoSIS");
        int aux = 5;
        for(String criterio:criterios){
            String nombre = obtenerCrierioEvaluacion(criterio).getNombreCriterio();
            String formula = recuperarFormula(criterio);
            if(formula!=null){
                nombre += "="+formula;
            }
            row.createCell(aux).setCellValue(nombre);
            aux++;
        }
        int rw = 2;
        for(GrupoAcademico g:grupos){
                ArrayList<String> usuarios = listarUsuariosGrupo(codigoEspacio,g);
                
                for(String us:usuarios){
                    int cl = 1;
                    ArrayList<Calificacion> notas = recuperarCalificaciones(codigoEspacio,us,criterios);
                    //se agrega nombre y otros datos
                    row = sheet.createRow(rw);
                    //cl++;
                    row.createCell(cl).setCellValue(g.getCodigoCarrera());//carrera.
                    cl++;
                    row.createCell(cl).setCellValue(g.getCodigoMateria());//materia.
                    cl++;
                    row.createCell(cl).setCellValue(g.getNumeroGrupo());//grupo.
                    cl++;
                    row.createCell(cl).setCellValue(us);//codigoSis.
                    cl++;
                    for(Calificacion c:notas){
                        //se agrega las notas.
                        if(tieneSubcriterios(c.getCodigoCriterio()) && estaEnLaBase(c.getNombreUsuario(), c.getCodigoEspacio(), c.getCodigoCriterio())){
                            HSSFCell cell = row.createCell(cl);
                            cell.setCellValue(c.getNombreCalificacion());
                            cell.setCellStyle(cellblue);
                            comment = true;
                        }else{
                            row.createCell(cl).setCellValue(c.getNombreCalificacion());
                        }
                        cl++;
                    }
                    //se pasa al siguiente usuario.
                    rw++;
                }
        }
        if(comment){
            row = sheet.createRow(rw+1);
            row.createCell(1).setCellValue("Las notas en color azul no fueron caluladas en base a su formula por que fueron subidas directamente.");
        }
        
        return new ByteArrayInputStream(res.getBytes());
    }
    
    public ArrayList<String> listarUsuariosGrupo(String codigoEspacio,GrupoAcademico g){
        ArrayList<String> res = new ArrayList<String>();
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select distinct(nombre_usuario) from asignacion_academica where codigo_espacio=? and codigo_carrera=? and codigo_materia=? and numero_grupo=?;");
            ps.setString(1, codigoEspacio);
            ps.setString(2, g.getCodigoCarrera());
            ps.setString(3, g.getCodigoMateria());
            ps.setString(4, g.getNumeroGrupo());
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                res.add(rs.getString("nombre_usuario"));
            }
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    public ArrayList<Calificacion> recuperarCalificaciones(String codigoEspacio,String usuario,ArrayList<String> criterios){
        ArrayList<Calificacion> res = new ArrayList<Calificacion>();
        for(String criterio:criterios){
            res.add(recuperarCalificacion(codigoEspacio,usuario,criterio));
        }
        return res;
    }
    public Calificacion recuperarCalificacion(String codigoEspacio,String nombreUsuario,String codigoCriterio){
        Calificacion res = null;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select nombre_calificacion from calificacion where codigo_espacio=? and nombre_usuario=? and codigo_criterio=?;");
            ps.setString(1, codigoEspacio);
            ps.setString(2, nombreUsuario);
            ps.setString(3, codigoCriterio);
            ResultSet rs = ps.executeQuery();
            
            
            if(rs.next()){
                //Si esta en la base devuelve directamente.
                res = new Calificacion(codigoEspacio, nombreUsuario, codigoCriterio, rs.getString("nombre_calificacion"));
                rs.close();
                ps.close();
                //c.close();
            }else{
                //Si no esta en la base verifica si tiene subcriterios sino devuelve sin asignar.
                rs.close();
                ps.close();
                //c.close();
                //CriterioEvaluacion ce = recuperarCriterioEvaluacion(codigoCriterio);
                if(tieneSubcriterios(codigoCriterio)){
                    int nota = calcularValor(nombreUsuario, codigoEspacio, codigoCriterio);
                    
                    if(nota < 0){
                        res = new Calificacion(codigoEspacio, nombreUsuario, codigoCriterio, "0");
                    }else{
                        res = new Calificacion(codigoEspacio, nombreUsuario, codigoCriterio, ""+nota);
                    }
                }else{
                    res = new Calificacion(codigoEspacio, nombreUsuario, codigoCriterio, "No tiene nota asignada");
                }
            }
            
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        System.out.println("criterio " + codigoCriterio + " nota " + res.getNombreCalificacion()+ " usuario "+nombreUsuario);
        return res;
    }
    
    private int calcularValor(String nombreUsuario,String codigoEspacio,String codigoCriterio){
        CriterioEvaluacion cri = recuperarCriterioEvaluacion(codigoCriterio);
        int res = -1;
        if(!cri.tieneSubcriterios()||estaEnLaBase(nombreUsuario,codigoEspacio,codigoCriterio)){
            try {
                Connection c = con.getConnection();
                PreparedStatement ps = c.prepareStatement("select nombre_calificacion from calificacion where codigo_espacio=? and nombre_usuario=? and codigo_criterio=?;");
                ps.setString(1, codigoEspacio);
                ps.setString(2, nombreUsuario);
                ps.setString(3, codigoCriterio);
                ResultSet rs = ps.executeQuery();
                if(rs.next()){
                    String valor = rs.getString("nombre_calificacion");
                    res = obtenerValor(codigoCriterio,valor);
                }
                rs.close();
                ps.close();
                //c.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }else{
            ArrayList<CriterioEvaluacion> sub = cri.getSubcriterios();
            HashMap<String,Double> map = new HashMap<String, Double>();
            for(CriterioEvaluacion ce:sub){
                map.put(ce.getNombreCriterio(), (double)calcularValor(nombreUsuario, codigoEspacio, ce.getCodigoCriterio()));
            }
            try{
                String formula = recuperarFormula(codigoCriterio);
                if(formula==null){
                    res = 0;
                }else{
                    Expresion exp = new Expresion(map, formula);
                    res = (int)exp.evaluar();
                }
            }catch(Exception ex){
                ex.printStackTrace();
            }
        }
        return res;
    }
    
    public int obtenerValor(String codigoCriterio,String valor){
        int res = -1;
        CriterioCalificacion cc = recuperarCriterioEvaluacion(codigoCriterio).getCriterioCalificacion();
        if(cc instanceof CriterioCalificacionCualitativo){
            res = (int)((CriterioCalificacionCualitativo)cc).getValores().get(valor).doubleValue();
        }else{
            res = Integer.parseInt(valor);
        }
        return res;
    }
    
    public boolean estaEnLaBase(String nombreUsuario,String codigoEspacio,String codigoCriterio){
        boolean res = false;
        try {
                Connection c = con.getConnection();
                PreparedStatement ps = c.prepareStatement("select nombre_calificacion from calificacion where codigo_espacio=? and nombre_usuario=? and codigo_criterio=?;");
                ps.setString(1, codigoEspacio);
                ps.setString(2, nombreUsuario);
                ps.setString(3, codigoCriterio);
                ResultSet rs = ps.executeQuery();
                res = rs.next();
                rs.close();
                ps.close();
                //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    
    
    public boolean tieneCriterioCalificacion(String codigoCriterioEvaluacion){
        boolean res = false;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_evaluacion where codigo_criterio=?;");
            ps.setString(1, codigoCriterioEvaluacion);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getString("nombre_criterio_calificacion")!=null){
                    res = true;
                }
            }
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    public CriterioCalificacion recuperarCriterioCalificacion(String codigoCriterioEvaluacion){
        CriterioCalificacion res = null;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_evaluacion where codigo_criterio=?;");
            ps.setString(1, codigoCriterioEvaluacion);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                res = obtenerCriterioCalificacion(rs.getString("nombre_criterio_calificacion"));
            }
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    
    public ArrayList<Calificacion> recuperarCalificaciones(String codigoEspacio,String nombreUsuario){
        ArrayList<CriterioEvaluacion> lista = listarCriteriosEspacio(codigoEspacio);
        ArrayList<String> criterios = new ArrayList<String>();
        for(CriterioEvaluacion ce:lista){
            criterios.add(ce.getCodigoCriterio());
        }
        return recuperarCalificaciones(codigoEspacio, nombreUsuario, criterios);
    }
    
    public boolean tieneSubcriterios(String codigoCriterioEvaluacion){
        boolean res = false;
        try {
            Connection c = con.getConnection();
            PreparedStatement ps = c.prepareStatement("select * from criterio_evaluacion where cri_codigo_criterio=?;");
            ps.setString(1, codigoCriterioEvaluacion);
            ResultSet rs = ps.executeQuery();
            res = rs.next();
            rs.close();
            ps.close();
            //c.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
    
    public static GestorCriteriosDB getInstance(){
        if(instance == null) instance = new GestorCriteriosDB();
        return instance;
    }

    public static void main(String[] args) {
        GestorCriteriosDB database = getInstance();

        /*LinkedList<Criterio> cola=new LinkedList<Criterio>();
        cola.add(ce);
        while(!cola.isEmpty())
        {
            Criterio c=cola.removeFirst();
            System.out.println(c.getNombreCriterio());
            if(c instanceof CriterioEvaluacion){
                CriterioEvaluacion cv=((CriterioEvaluacion)c);
                for(Criterio cx: cv.getSubcriterios()){
                    cola.add(cx);
                }
            }
        }*/
        //database.quitarCriterioCalificacion("CRI6");
        //database.guardarFormula("CRI6", "A+B+C");
        //database.crearCriterioEvaluacion("ESP4");
        /*ArrayList<CriterioEvaluacion> res = database.listarCriteriosEspacio("ESP3");
        for(CriterioEvaluacion ce:res){
            System.out.println(ce.getNombreCriterio());
        }*/
        /*ArrayList<Calificacion> cs = new ArrayList<Calificacion>();
        cs.add(new Calificacion("ESP4", "199800224", "CRI20", "100"));
        try {
            database.guardarCalificaciones(cs);
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }*/
        ArrayList<GrupoAcademico> gs = new ArrayList<GrupoAcademico>();
        gs.add(new GrupoAcademico("378501", "2008141", "1"));
        ArrayList<String> cs = new ArrayList<String>();
        cs.add("CRI46");
        /*HSSFWorkbook work = database.recuperarCalificaciones("ESP16", gs, cs);
        try {
        FileOutputStream out = new FileOutputStream("c:\\Documents and Settings\\Rodrigo\\Escritorio\\workbook.xls");
        work.write(out);
        out.close();    
        } catch (Exception ex) {
            ex.printStackTrace();
        }*/
        
        //InputStream is = database.recuperarCalificaciones("ESP40", gs, cs);

        /*CriterioEvaluacion ce = database.recuperarCriterioEvaluacion("CRI30");
        System.out.println(ce.getSubcriterios().size());*/
        //System.out.println(database.listarCriteriosEspacio("ESP16").size());
        /*ArrayList<Calificacion> res = database.recuperarCalificaciones("ESP16", "200002412");
        for(Calificacion c:res){
            System.out.println(c.getNombreCriterioEvaluacion()+"       "+c.getNota());
        }*/
        //System.out.println(database.tieneSubcriterios("CRI23"));
        //System.out.println(database.recuperarCriterioEvaluacion("CRI58").getSubcriterios().size());
    }
}
